"""
时间: 2019-10-09 11:47:38
作者: 李幸南 (l1xnan@qq.com)
描述：合并Excel表 - 同名工作簿合并到一张工作簿中
"""

import time
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Font, colors
from openpyxl.worksheet.worksheet import Worksheet
from xlrd import open_workbook

normal_ft = Font(name="微软雅黑", size=10)
strong_ft = Font(name="微软雅黑", size=10, color=colors.RED)


def get_excel_files(path: str):
    excel_files = [*Path(path).glob("./*.xlsx"), *Path(path).glob("./*.xls")]
    return [file for file in excel_files if "~$" not in str(file)]


def merge_excel(path: str, template=None, desc="result", callback=print):
    """
    合并Excel表 - 同名工作簿合并到一张工作簿中
    :param path: 文件路径
    :param template: 模板文件
    :param desc: 输出文件名
    :param callback: 用于输出
    """
    if template is None:
        return
    start_time = time.time()
    template_book = open_workbook(template)
    sheet_names = template_book.sheet_names()
    files = get_excel_files(path)
    total = len(files)
    book = Workbook()
    log = book.active
    log.title = '__输出日志__'

    # 输出日志表中添加首行数据：模板, 模板工作表名称
    for i, val in enumerate(['模板', *sheet_names], start=1):
        cell = log.cell(1, i)
        cell.value = val
        cell.font = normal_ft

    # 按照模板工作簿名称，创建工作簿，并添加表头
    for name in sheet_names:
        sheet = book.create_sheet(name)
        ws = template_book.sheet_by_name(name)
        sheet.append(ws.row_values(0))

    error = 0
    for i, file in enumerate(files, start=1):
        wb = open_workbook(file, on_demand=True)
        error_names = []
        names = wb.sheet_names()
        cell = log.cell(i + 1, 1)
        cell.value = str(file.name)
        cell.font = normal_ft
        for n, name in enumerate(names, start=1):
            start_row = 1
            try:
                sheet: Worksheet = book[name]
            except:
                error_names.append(name)
                start_row = 0
                try:
                    sheet = book[f"异常-{name}"]
                except:
                    sheet = book.create_sheet(title=f"异常-{name}")
            finally:
                cell = log.cell(i + 1, n + 1)
                cell.value = name
                cell.font = normal_ft if start_row else strong_ft
            if not start_row:
                sheet.append([f"异常文件：{file.name}"])
            ws = wb.sheet_by_name(name)
            for j in range(start_row, ws.nrows):
                values = ws.row_values(j)
                sheet.append(values)
        msg = f"解析成功：{file.name}"
        if error_names:
            error += 1
            msg = f"解析异常：{file.name}"
        callback({"total": total, "progress": i, "msg": msg})
    exec_time = int(time.time() - start_time)
    callback(f"共计{total}个文件，成功{total - error}个，异常{error}个，合计耗时:{exec_time}s")
    path = Path(path).resolve()
    desc_path = path.parent.joinpath(path.parts[-1] + "-out")
    desc_path.mkdir(exist_ok=True)
    desc_file = desc_path.joinpath(f"{desc}.xlsx")
    book.save(desc_file)
    callback(f"合并完成：{desc_file}")
    return desc_file


if __name__ == "__main__":
    merge_excel("./tmp", desc="result1")
